/****** Object:  StoredProcedure [dbo].[GetArraPIQuestionnaire]    Script Date: 09/17/2009 10:51:31 ******/
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[GetArraPIQuestionnaire]')
                    AND type IN ( N'P', N'PC' ) ) 
    DROP PROCEDURE [dbo].[GetArraPIQuestionnaire]
GO

/****** Object:  StoredProcedure [dbo].[GetArraPIQuestionnaire]    Script Date: 09/17/2009 10:51:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		eivaskevicius
-- Create date: 9/16/2009
-- Description:	Gets data for ARRA PI questionnaire page.
-- =============================================
CREATE PROCEDURE [dbo].[GetArraPIQuestionnaire]
    @id INT,
    @isPIQuestionnaireID BIT
AS 
    BEGIN
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
        SET NOCOUNT ON ;
        
        -- Select questionnaire data
        SELECT TOP 1
                dbo.ArraPIQuestionnaire.ID AS ArraPIQuestionnaireID,
                dbo.ArraPIQuestionnaire.ArraAgreementID,
                dbo.ArraPIQuestionnaire.Narrative,
                dbo.ArraPIQuestionnaire.QuarterlyActivities,
                dbo.ArraPIQuestionnaireEventHistory.ArraPIQuestionnaireEventID,
                ( COALESCE(dbo.AgrAgreement_Fct.PIFirstName + ' ', '')
                  + COALESCE(dbo.AgrAgreement_Fct.PIMiddleName + ' ', '')
                  + COALESCE(dbo.AgrAgreement_Fct.PILastName, '') ) AS PIName,
                dbo.AgrAgreement_Fct.ShortTitle,
                dbo.AgrAgreement_Fct.FundNumber,
                dbo.AgrAgreement_Fct.ChiefCode,
                dbo.AgrAgreement_Fct.Institution,
                ( COALESCE(dbo.FwkDomainOrganization.Description + ' ', '')
                  + COALESCE(dbo.FwkDomainOrganization.ChiefCode, '') ) AS Department,
                dbo.AgrAgreement_Fct.SponsorName,
                dbo.AgrAgreement_Fct.SponsorNumber,
                dbo.ArraQuarterlyReportStatus.QuarterEndDate,
                ( SELECT    COALESCE(dbo.AdmPerson.FirstName + ' ', '')
                            + COALESCE(dbo.AdmPerson.MiddleName + ' ', '')
                            + COALESCE(dbo.AdmPerson.LastName, '')
                  FROM      dbo.AdmPerson
                  WHERE     dbo.AdmPerson.Id = dbo.ArraAgreement.DepartmentManagerAdmPersonID
                ) AS DepartmentAdmin,
                ( SELECT    COALESCE(dbo.AdmPerson.FirstName + ' ', '')
                            + COALESCE(dbo.AdmPerson.MiddleName + ' ', '')
                            + COALESCE(dbo.AdmPerson.LastName, '')
                  FROM      dbo.AdmPerson
                  WHERE     dbo.AdmPerson.Id = dbo.ArraAgreement.PreAwardManagerAdmPersonID
                ) AS ResearchMgmtAdmin
        FROM    dbo.ArraAgreement
                INNER JOIN dbo.ArraPIQuestionnaire ON dbo.ArraAgreement.ID = dbo.ArraPIQuestionnaire.ArraAgreementID
                INNER JOIN dbo.ArraPIQuestionnaireEventHistory ON dbo.ArraPIQuestionnaire.ID = dbo.ArraPIQuestionnaireEventHistory.ArraPIQuestionnaireID
                INNER JOIN dbo.AgrAgreement_Fct ON dbo.ArraAgreement.FundID = dbo.AgrAgreement_Fct.FundId
                INNER JOIN dbo.FwkDomainOrganization ON dbo.AgrAgreement_Fct.OrganizationId = dbo.FwkDomainOrganization.Id
                INNER JOIN dbo.ArraQuarterlyReportStatus ON dbo.ArraAgreement.ArraQuarterlyReportStatusID = dbo.ArraQuarterlyReportStatus.ID
        WHERE   CASE WHEN @isPIQuestionnaireID = 1
                     THEN dbo.ArraPIQuestionnaire.ID
                     ELSE dbo.ArraAgreement.ID
                END = @id
        ORDER BY dbo.ArraPIQuestionnaireEventHistory.EventDate DESC

        RETURN @@ERROR ;

    END

GO


